#!/usr/bin/python
# -*- coding: utf-8 -*-
import pymysql
import warnings
import datetime
import sys

class handledata():
    def __init__(self):
        warnings.filterwarnings("ignore")
        self.db = pymysql.connect("localhost", "lee", "lee", "marketing", charset='utf8' )
        self.cursor = self.db.cursor()



    def loadDerivedTag(self):
        sql = "select t1.tag_code,operation_category,operation,value,derived_tag_code from rel_derived_tag t1,bas_tag t2 where t1.tag_code = t2.tag_code and t2.status = 'on';"
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        dataList = []
        for row in data:
            rowMap = {}
            rowMap['tag_code'] =  row[0]
            rowMap['operation_category'] =  row[1]
            rowMap['operation'] =  row[2]
            rowMap['value'] =  row[3]
            rowMap['derived_tag_code'] =  row[4]
            dataList.append(rowMap)

        return dataList

    def getHiveSqlPre(self,data):
        fields = []
        fieldsStr = []
        for rowMap in data:
            if rowMap['operation_category'] == 'compare':
                fields.append([rowMap['tag_code'],rowMap['operation'],rowMap['value']])
                fieldsStr.append('case when ' + rowMap['tag_code'] + ' ' + rowMap['operation'] + ' ' +  rowMap['value'] + ' then 1 else 0 end as '+ rowMap['derived_tag_code'])
            elif rowMap['operation_category'] == 'judge':
                operation = rowMap['operation'] == '✔' and 'yes' or 'no'
                fields.append([rowMap['tag_code'],operation])
                fieldsStr.append('case when ' + rowMap['tag_code'] + ' = \'' + operation + '\' then 1 else 0 end as '  + rowMap['derived_tag_code'])
            else:
                value=','.join(map(lambda x:'\''+x+'\'',rowMap['value'].split(',')))
                fields.append([rowMap['tag_code'],rowMap['operation'],rowMap['value'].split(',')])
                fieldsStr.append('case when ' + rowMap['tag_code'] + ' '+ rowMap['operation'] + ' (' + value + ') then 1 else 0 end as ' + rowMap['derived_tag_code'])
        return fields,fieldsStr

    def getHiveSql(self):
        data = self.loadDerivedTag()
        fields,fieldsStr = self.getHiveSqlPre(data)
        ds=datetime.datetime.now().strftime('%Y%m%d')
        for i in range(len(fields)):
            print(i,fields[i])
            print(i,fieldsStr[i])

        sql='''
drop table if exists crowd_portrait;
create table crowd_portrait
as select %s
from marketing.crowd_portrait where ds = '%s';
        '''%(',\n'.join(fieldsStr),ds)
        return sql

if __name__ == '__main__':
    if(len(sys.argv) < 2):
       print ("缺少参数：存放sql的路径")
       sys.exit(1)
    path = sys.argv[1]
    handledata = handledata()
    sql = handledata.getHiveSql()
    print(sql)
    with open(path,'w') as f:
        f.write(sql.encode('utf-8'))